E-Tourism
Creating Database in MS Access
Creating Database and Querying the table
Database is the most efficient way for storing data, ease retrieval and updates of the data. Here, a simple database table is created in MS Access and shown how summary tables are created.
Table to store data of guest of hotel is created. Attributes of guests considered were guest name, city, country, gender and email. Some records of the guests were inserted to the table. From the stored records in the guest table summary tables were created. All the steps of creating table, inserting records, creating queries are shown.
The summary tables like:
- Number of guest from each country
- Number of guest from each city of each country
- Number of male and female guests
- Number of male or female guests by country
will be of great value for the hotel management. These summary tables help then to know from which the country maximum number of guests are coming; there are more male guests or female guests. Then based on the summary information the hotel management can make marketing strategies; strategies to increase the number of guests from the countries from which sufficient guests are not coming, to retain guests etc.
Creating tables and querying in MS Access
- Create a blank database in MS Access
- Click create option from the menu bar
- Click on Table Design button in Tables Panel
- A Table with three columns (Field name, data type and description) to specify attributes and the type of attributes will appear
- Write name of attribute in Field Name; Select Data Type from Data Type Drop Down List. No need to specify description.
Here, we have to create table to store data about guests so, attributes of guest are considered are guestid, guest_name, city, country, email, gender. Type in the table design like in the given image.
- Right Click on the guestid attribute and select primary key option from the pop menu.
- Save the table in name of "guest"
- Table in the name of guest will be created and seen in the left panel of Access (All Access Objects).
Inserting some records in the guest table
- Double click on the "guest" table from the left panel: (All Access Objects)
- Insert some records in the table
Now let's write some queries:
Click on create menu in the standard tool bar
- Click Query Design from the Queries tab
- Show Table menu will be visible, select guest table and click ADD button. Close the show table window.
Let's write query to view number of guests coming from each country:
- Double click on the country attribute from the guest table. Country attribute will be seen in the table below like shown in picture. Then click on the Summation button (found in the query design tab). The Total row will be visible. In the total row of the country column group by clause will be seen. Then in the column next to country type Total: count(*) in the Field row, Type Expression in the total row of this column.
Then click RUN button in the Query Design Panel. Summary Table showing number of guests by each country will be displayed.
Number of guests from each country by gender
- Select Query Design from the Queries tab
- Show Table menu will be visible, select guest table and click ADD button. Close the show table window.
- Double click on the country attribute from the guest table. Country attribute will be seen in the table below like shown in the picture, then double click on the gender attribute of the table and it will also be seen in the table below. Then click on the Summation button in the query design tab (This will be seen only in the query design view). Then Totalrow will appear, in the country and gender columns group by clause will be seen. Then in the third column type, total_guest:count(*) in the Fields row ; in the total for the third column type expression total_guest: count(*).
- Then click RUN button from the Query Design Panel. Summary Table showing number of guest by country will be displayed.
Displaying information of male guests from Nepal only
- Click on create menu in the tool bar.
- Select Query Design from the Queries tab
- New sub-window will be visible with show Table menu, select guest table and click ADD button. The click on the close button of the show table window.
- Then double click on the country, gender, guestname, email attributes of the guest table; all of these will be added to the table below.
- Then type Nepal in the criteria row of the country column and type male in the criteria row of the gender column as shown in the figure below.
- all the check boxes in the show row of the table will be selected, if unselected then that column will not be seen in the resulting table.
Then click RUN button from the Query Design Panel. Summary Table showing number of guest by country will be displayed.
